package com.zhouheng.springbootjpa.repository;

import com.zhouheng.springbootjpa.entity.Rain;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;

public interface RainRepository extends JpaRepository<Rain,String> {

    List<Rain> findByObserveTime(Date observeTime);

    @Query(value = "select " +
            "ID,STATION_ID, " +
            "SUM(if(CAST(VALUE AS DECIMAL)>1000,'0',VALUE))/count(*) as VALUE," +
            "OBSERVE_TIME " +
            "from rain " +
            "where " +
            "OBSERVE_TIME >= ?1 " +
            "and OBSERVE_TIME <= ?2 " +
            "group by STATION_ID",
            nativeQuery = true)
    List<Rain> findSumPreByObserveTime(String startTime,String endTime);

}
